POI实现excel的导入导出-[采用反射实现的版本]

前段时间学习了POI实现excel的导入导出-[未采用反射实现的版本]的文章，觉得不采用反射实现非常不合理，在祥哥【我眼中的大佬】的指导下实现了POI实现excel的导入导出-[采用反射实现的版本]的实现。
A、具体的要点如下:

---

1、采用泛型，增加代码的灵活性和可扩展性；
2、使用适配器模式；
3、使用反射自动执行对应字段的方法；
4、绑定字段的值和表头文字【采用linkedhashmap()】实现；

B、代码

---

ExcelVO.java

package com.spring.boot.gp4zj.util;

import java.util.List;
import java.util.Map;

/**
 * excel导入导出相关的必须信息;
 * 
 * @author xdsm
 *
 */
public class ExcelVO<T> {
	// 表头列表;
	Map<String, String> workBookHeaderFields = null;
	// excel中的数据值对象;
	List<T> workBookVos = null;
	// 泛型数据对象;
	T clazzRes = null;
	
	public List<T> getWorkBookVos() {
		return workBookVos;
	}

	public void setWorkBookVos(List<T> workBookVos) {
		this.workBookVos = workBookVos;
	}

	public Map<String, String> getWorkBookHeaderFields() {
		return workBookHeaderFields;
	}

	public void setWorkBookHeaderFields(Map<String, String> workBookHeaderFields) {
		this.workBookHeaderFields = workBookHeaderFields;
	}

	public T getClazzRes() {
		return clazzRes;
	}

	public void setClazzRes(T clazzRes) {
		this.clazzRes = clazzRes;
	}
}

POIUtils.java

package com.spring.boot.gp4zj.util;

import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.spring.boot.gp4zj.model.AttenceDetail;
import com.spring.boot.gp4zj.model.ExcelVOMapper;

/**
 * Excel操作相关的工具类;
 * 
 * @author:JunZhou
 * @Company:LongZheng
 * @Email:1769676159@qq.com
 * @2018年1月16日@下午3:19:25
 */
public class POIUtils2 {
	public static void main(String[] args) throws Exception {
		/*
		 * AttenceDetail clazzRes = new AttenceDetail(); // 测试数据;
		 * ExcelVO<AttenceDetail> excelVo = new ExcelVO<AttenceDetail>();
		 * excelVo.setClazzRes(clazzRes);
		 * 
		 * @SuppressWarnings("unused") List<String> workBookHeaderFields = new
		 * ArrayList<String>(); final Map<String, String> HEADER_FIELDS = new
		 * LinkedHashMap<String, String>(); HEADER_FIELDS.put("考勤日期",
		 * "attencedTime"); HEADER_FIELDS.put("考勤名称", "attencerName");
		 * HEADER_FIELDS.put("员工编码", "attencerCode"); HEADER_FIELDS.put("出勤时长",
		 * "workTimeLen"); HEADER_FIELDS.put("加班时长", "extraTimeLen");
		 * HEADER_FIELDS.put("合计出勤时长", "totalTimeLen");
		 * HEADER_FIELDS.put("报工单位", "applyCompany"); HEADER_FIELDS.put("结算单位",
		 * "payCompany"); HEADER_FIELDS.put("创建人", "creator");
		 * HEADER_FIELDS.put("出勤人天", "workDayNum"); HEADER_FIELDS.put("加班人天",
		 * "extraDayNum"); HEADER_FIELDS.put("合计人天", "totalDayNum");
		 * HEADER_FIELDS.put("是否删除汇总表", "isDelGather");
		 * HEADER_FIELDS.put("是否确认", "isConfirmed"); HEADER_FIELDS.put("创建时间",
		 * "createTime"); HEADER_FIELDS.put("修改时间", "editTime"); // 生成测试数据;
		 * excelVo.setWorkBookHeaderFields(HEADER_FIELDS); List<AttenceDetail>
		 * workBookVos = TestDataGenerater.attenceDetailTestDatasGenerater(2);
		 * excelVo.setWorkBookVos(workBookVos); createExcel(excelVo);
		 */
		String excelPath = "C:/Users/xdsm/Desktop/Excel-900500219.xls";
		AttenceDetail attenceDetailClazz = new AttenceDetail();
		List<AttenceDetail> attenctDetailList = readExcel(excelPath, attenceDetailClazz);
		for (AttenceDetail attenceDetail : attenctDetailList) {
			System.out.println(attenceDetail);
		}
	}

	/**
	 * 创建Excel
	 * 
	 * @param <T>
	 * 
	 * @param list
	 *            数据
	 */
	@SuppressWarnings({ "unchecked", "unused" })
	public static <T> HSSFWorkbook createExcel(ExcelVO<T> excelVo) {
		@SuppressWarnings("rawtypes")
		Class clazz = excelVo.getClazzRes().getClass();
		// 表头信息列表;
		List<String> workBookHeaderFields = new ArrayList<String>();
		Map<String, String> headerFields = excelVo.getWorkBookHeaderFields();
		// 表格数据信息列表;
		List<T> dataList = excelVo.getWorkBookVos();
		// 创建一个Excel文件
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 创建一个工作表
		HSSFSheet sheet = workbook.createSheet("考勤明细表");
		// 添加表头行
		HSSFRow hssfRow = sheet.createRow(0);
		// 设置单元格格式居中
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 添加表头内容
		// -------第一行第0-2列---------
		// 设置表头的相关信息;
		int headTitleIndex = 0;
		HSSFCell headCell = null;// 此处使用同一个引用对象，可以节省内存;
		for (Entry<String, String> vo : headerFields.entrySet()) {
			// 表头的名称字段;
			if (headTitleIndex == 0) {
				headCell = hssfRow.createCell(headTitleIndex);
				headCell.setCellValue(vo.getKey());
				headCell.setCellStyle(cellStyle);
			} else {
				headCell = hssfRow.createCell(headTitleIndex);
				headCell.setCellValue(vo.getKey());
				headCell.setCellStyle(cellStyle);
			}
			headTitleIndex++;
		}
		// 填充数据到工作簿;
		for (int i = 0; i < dataList.size(); i++) {
			// 创建数据行;
			hssfRow = sheet.createRow((int) i + 1);
			T attenceDetail = dataList.get(i);
			// 创建单元格，并设置值
			int fieldVoIndex = 0;
			HSSFCell cell = null;// 此处使用同一个引用对象，可以节省内存;
			for (Entry<String, String> vo : headerFields.entrySet()) {
				// 字段值取值器;
				String fieldVoGetter = "get" + (vo.getValue()).substring(0, 1).toUpperCase()
						+ (vo.getValue()).substring(1);
				cell = hssfRow.createCell(fieldVoIndex);
				// TODO Auto-generated
				Method method;
				try {
					String invokeResultStr = null;
					method = clazz.getMethod(fieldVoGetter);
					Object methodResult = method.invoke(attenceDetail);
					if (methodResult != null) {
						invokeResultStr = methodResult.toString();
					}
					cell.setCellValue(invokeResultStr);
					cell.setCellStyle(cellStyle);
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				// method.invoke(clazz.newInstance());
				fieldVoIndex++;
			}
		}

		return workbook;
	}

	/**
	 * 接收上传的excel并读取其中的信息;
	 * 
	 * @throws IllegalAccessException
	 * @throws Exception
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public static <T> List<T> readExcel(String excelPath, T clazzRes) throws Exception {
		Class clazz = clazzRes.getClass();
		List<T> list = new ArrayList<T>();
		HSSFWorkbook workbook = null;
		try {
			// 读取Excel文件
			InputStream inputStream = new FileInputStream(excelPath);
			workbook = new HSSFWorkbook(inputStream);
			inputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		// 循环工作表
		for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = workbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			// 循环行
			for (int rowNum = 1; rowNum < hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow == null) {
					continue;
				}
				// 填充数据到行;
				Map<String, String> HEADER_FIELDS = ExcelVOMapper.getAttenceDetailMapper();
				HSSFCell cell = null;// 公用cell，节省内存的开销;
				// 每行的数据对象;
				T dataVo = (T) clazz.newInstance();
				int fieldVoIndex = 0;
				for (Entry<String, String> vo : HEADER_FIELDS.entrySet()) {
					// 字段值取值器;
					String fieldVoSetter = "set" + (vo.getValue()).substring(0, 1).toUpperCase()
							+ (vo.getValue()).substring(1);
					// TODO Auto-generated
					Method method;
					try {
						cell = hssfRow.getCell(fieldVoIndex);
						if (cell == null) {
							continue;
						}
						method = clazz.getMethod(fieldVoSetter, String.class);
						String cellValue = cell.getStringCellValue();
						/*
						 * if(cellValue==null||"".equals(cellValue)){ // 空数据列测试;
						 * if (fieldVoIndex == 1) { throw new
						 * MyException("员工编码不能为空"); } }
						 */
						// System.out.println(cellValue);
						method.invoke(dataVo, cellValue);
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					fieldVoIndex++;
				}
				list.add(dataVo);
			}
		}
		return list;
	}

	/**
	 * 获取指定数据类型的所有声明字段;
	 * 
	 * @param dataObj:泛型对象;
	 * @return:返回字段列表【String】;
	 */
	public static <T> List<String> getAllFields(T dataObj) {
		Class<? extends Object> dataObjClazz = dataObj.getClass();
		Field[] fields = dataObjClazz.getDeclaredFields();
		List<String> fieldsList = new ArrayList<String>();
		for (int i = 0; i < fields.length; i++) {
			String fieldStr = fields[i].toString();
			String realFieldStr = fieldStr.substring(fieldStr.lastIndexOf(".") + 1);
			fieldsList.add(realFieldStr);
		}
		return fieldsList;
	}

	/**
	 * 获取对象中的所有已声明的方法名;
	 * 
	 * @param dataObj:泛型对象;
	 * @return:返回方法列表【String】;
	 */
	public static <T> List<String> getAllMethods(T dataObj) {
		List<String> methodsList = new ArrayList<String>();
		Class<? extends Object> dataObjClazz = dataObj.getClass();
		Method[] methods = dataObjClazz.getDeclaredMethods();
		for (int i = 0; i < methods.length; i++) {
			String fieldStr = methods[i].toString();
			if (fieldStr.contains("get")) {
				String realFieldStr = fieldStr.substring(fieldStr.lastIndexOf(".") + 1, fieldStr.lastIndexOf("("));
				methodsList.add(realFieldStr);
			}
		}
		return methodsList;
	}

	/**
	 * 获取对象中的所有已声明的方法名;
	 * 
	 * @param dataObj:泛型对象;
	 * @return:返回方法列表【String】;
	 */
	public static <T> List<String> getAllSetMethods(T dataObj) {
		List<String> methodsList = new ArrayList<String>();
		@SuppressWarnings("unused")
		Class<? extends Object> dataObjClazz = dataObj.getClass();
		List<String> methods = getAllFields(dataObj);
		for (int i = 0; i < methods.size(); i++) {
			String fieldStr = methods.get(i).toString();
			String methodName = "set" + fieldStr.substring(0, 1).toUpperCase() + fieldStr.substring(1);
			methodsList.add(methodName);
		}
		return methodsList;
	}
}

C、使用要点

---

1、本工具类的使用前提是所有的字段均采用驼峰命名方式，且前两个字母为小写【因为eclipse自动生成的getter和setter会根据前两个字母进行自适应处理，并非固定模式】
2、本工具类并未对导入数据进行全面的非空或者类型等校验，具体的校验可自行添加。
3、本工具类因为采用的是反射是想，因此执行效率较低;
D、参考文章.
设计模式–观察者模式初探和java Observable模式
EasyPoi教程V1.0

赏

谢谢你请我吃糖果